﻿'Description:装试新工时分配
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:yuxj
'create Date:2023-04-23
'Modify by yuxj
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Public Class WorkerStepTimeManageZSBusiness
    Public Function GetEmployees(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strsql As String =
            <string>
                select e.employeename,e.fullname from employee e
                left join teamemployee te on te.employeeid=e.employeeid
                left join team t on t.teamid=te.teamid
                left join shift s on s.shiftid=t.shiftid
                where 1=1
            </string>
        If para.ContainsKey("teamId") Then
            strsql += String.Format(" and t.teamid='{0}'", para("teamId"))
        End If

        If para.ContainsKey("shiftId") Then
            strsql += String.Format(" and s.shiftId='{0}'", para("shiftId"))
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 查询工时数据列表
    ''' add:yuxj20230424
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetEmpWorkTimeSumInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strSql As String =
            <string>
                select ew.employeeid,e.fullname,e.employeename,wc.workcentername,s.shiftname,t.teamname,
                       ew.sumworktime,ew.splitouttime,ew.splitintime,ew.employeeworktimezsid,
                       ew.sumworktime-nvl(ew.splitouttime,0)+nvl(ew.splitintime,0) totalworktime,
                       f.factoryid,wc.workcenterid,s.shiftid,t.teamid 
                from employeeworktimezs ew
                left join employee e on e.employeeid=ew.employeeid
                left join teamemployee te on te.employeeid=e.employeeid
                left join team t on t.teamid=te.teamid
                left join shift s on s.shiftid=t.shiftid
                left join workcenter wc on wc.workcenterid=s.workcenterid
                left join factory f on f.factoryid=wc.factoryid 
                where 1=1
            </string>
        If para.ContainsKey("factoryId") Then
            strSql += String.Format(" and f.factoryId = '{0}' ", para("factoryId"))
        End If

        If para.ContainsKey("workcenterId") Then
            strSql += String.Format(" and wc.workcenterId = '{0}' ", para("workcenterId"))
        End If

        If para.ContainsKey("shiftId") Then
            strSql += String.Format(" and s.shiftId = '{0}' ", para("shiftId"))
        End If

        If para.ContainsKey("teamId") Then
            strSql += String.Format(" and t.teamId = '{0}' ", para("teamId"))
        End If

        If para.ContainsKey("employeeName") Then
            strSql += String.Format(" and e.employeeName = '{0}' ", para("employeeName"))
        End If
      
        If para.ContainsKey("startDate") Then
            strSql += String.Format(" and ew.startdate = '{0}' ", para("startDate"))
        End If

        If para.ContainsKey("endDate") Then
            strSql += String.Format(" and ew.enddate = '{0}' ", para("endDate"))
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)
        Return result
    End Function

    Public Function GetWorkTimeEmpInfo(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strSql As String =
            <string>
                select e.employeeid,e.fullname,e.employeename
                from  employee e 
                left join teamemployee te on te.employeeid=e.employeeid
                left join team t on t.teamid=te.teamid
                left join shift s on s.shiftid=t.shiftid
                left join workcenter wc on wc.workcenterid=s.workcenterid
                left join factory f on f.factoryid=wc.factoryid 
                where 1=1
            </string>
        If para.ContainsKey("factoryId") Then
            strSql += String.Format(" and f.factoryId = '{0}' ", para("factoryId"))
        End If

        If para.ContainsKey("workcenterId") Then
            strSql += String.Format(" and wc.workcenterId = '{0}' ", para("workcenterId"))
        End If

        If para.ContainsKey("shiftId") Then
            strSql += String.Format(" and s.shiftId = '{0}' ", para("shiftId"))
        End If

        If para.ContainsKey("teamId") Then
            strSql += String.Format(" and t.teamId = '{0}' ", para("teamId"))
        End If

        If para.ContainsKey("employeeName") Then
            strSql += String.Format(" and e.employeeName = '{0}' ", para("employeeName"))
        End If

        Dim dtEmp As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Return dtEmp
    End Function
    ''' <summary>  
    ''' 获取人员--获取人员工时--检查人员是否存在，更新工时数据
    ''' add:yuxj20230509计算工时
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CalcEmpWorkTimeSumInfo(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim dt As DataTable = GetWorkTimeEmpInfo(para) '获取人员
        Dim strEmps As String = ""
        For Each dr In dt.Rows
            strEmps += dr("employeeid") + ","
        Next
        strEmps = strEmps.Trim(",")
        Dim strSql As String =
            <string>
                select workerid,sum(duration) duration from (
                        select csts.workerid, decode(csts.checktype,
                                      '2',
                                      round(nvl(SI.Durationperunit2,0) / NVL(CSTS.SPLITCOUNT, 1), 2),
                                      round(nvl(SI.Durationperunit,0) / NVL(CSTS.SPLITCOUNT, 1), 2))-nvl(splitduration,0) duration 
                          from containersteptimesheet csts
                          left join (select ct.containersteptimesheetid,
                                            sum(nvl(ct.splitduration, 0)) splitduration
                                       from containersteptimesheetsplit ct
                                      group by ct.containersteptimesheetid) ct
                            on ct.containersteptimesheetid = csts.containersteptimesheetid
                          left join stepinfo si
                            on si.stepinfoid = csts.stepinfoid   
                          where csts.factoryid='{0}' and csts.workerid in ('{1}') and csts.checkdate between to_date('{2}','yyyy-mm-dd') and to_date('{3}','yyyy-mm-dd') +1
                        union all
                        select cstsd.workerid,nvl(cstsd.splitduration,0) duration from containersteptimesheetsplit cstsd
                          left join containersteptimesheet csts
                            on csts.containersteptimesheetid = cstsd.containersteptimesheetid
                        where csts.factoryid='{0}' and csts.workerid in ('{1}') and csts.checkdate between to_date('{2}','yyyy-mm-dd') and to_date('{3}','yyyy-mm-dd') +1
                ) group by workerid
            </string>
        strSql = String.Format(strSql, para("factoryId"), strEmps.Replace(",", "','"), para("startDate"), para("endDate"))
        dt = DatabaseHelper.Query_ReturnDatTable(strSql) '获取工时表人员工时

        '判断该时段人员工时是否存在
        Dim strSqlExist = "select employeeworktimezsid from employeeworktimezs where employeeid='{0}' and startdate='" + para("startDate") + "' and enddate='" + para("endDate") + "'"
        '不存在时执行插入
        Dim strSqlInsert As String = " insert into employeeworktimezs(employeeid, sumworktime, startdate, enddate,splitouttime, splitintime,employeeworktimezsid) values('{0}',{1},'{2}','{3}',0,0,'{4}')"
        '更新工时
        Dim strSqlUpdate As String = " update employeeworktimezs ew set sumworktime={0} where employeeworktimezsid='{1}' "

        Dim dtExist As DataTable = New DataTable
        For Each dr In dt.Rows
            dtExist = DatabaseHelper.Query_ReturnDatTable(String.Format(strSqlExist, dr("workerid")))
            If dtExist.Rows.Count > 0 Then
                DatabaseHelper.ExecuteSql(String.Format(strSqlUpdate, dr("duration"), dtExist.Rows(0)("employeeworktimezsid")))
            Else
                DatabaseHelper.ExecuteSql(String.Format(strSqlInsert, dr("workerid"), dr("duration"), para("startDate"), para("endDate"), System.Guid.NewGuid()))
            End If
        Next
        Return True
    End Function

    ''' <summary>
    ''' 获取系统设置的时段参数
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function LoadDates(ByVal para As Dictionary(Of String, String)) As DataTable
        Try
            Dim strsql As String =
            <string>
                select u.attributevalue
                  from USERATTRIBUTE U
                 where u.parentid = '{0}'
                   and u.userattributename = '{1}'                
            </string>
            strsql = String.Format(strsql, para("factoryId"), para("userattributename"))
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
            Dim strValue As String = ""
            If dt.Rows.Count > 0 Then
                strValue = dt.Rows(0)("attributevalue")
            End If

            Dim dtRes As DataTable = New DataTable
            Dim begindate As String = ""
            Dim enddate As String = ""

            dtRes.Columns.Add("begindate")
            dtRes.Columns.Add("enddate")
            Dim nowDay As Integer = Date.Now.Day
            If String.IsNullOrWhiteSpace(strValue) Or strValue.Split("-").Length = 0 Then
                '没有配置时取默认值，当前时间<=20号 上月21号到本月20号 当前时间>20号 本月21号到下月20号
                If nowDay >= 21 Then
                    begindate = Date.Now.Year.ToString + Date.Now.Month.ToString.PadLeft(2, "0") + "21"
                    enddate = Date.Now.Year.ToString + Date.Now.AddMonths(1).Month.ToString.PadLeft(2, "0") + "20"
                Else
                    begindate = Date.Now.Year.ToString + Date.Now.AddMonths(-1).Month.ToString.PadLeft(2, "0") + "21"
                    enddate = Date.Now.Year.ToString + Date.Now.Month.ToString.PadLeft(2, "0") + "20"
                End If
            Else
                begindate = strValue.Substring(0, strValue.IndexOf("-"))
                enddate = strValue.Substring(strValue.IndexOf("-") + 1)
                If nowDay >= Integer.Parse(enddate) Then '有可能格式设置错误导致异常
                    begindate = Date.Now.Year.ToString + Date.Now.Month.ToString.PadLeft(2, "0") + begindate
                    enddate = Date.Now.Year.ToString + Date.Now.AddMonths(1).Month.ToString.PadLeft(2, "0") + enddate
                Else
                    begindate = Date.Now.Year.ToString + Date.Now.AddMonths(-1).Month.ToString.PadLeft(2, "0") + begindate
                    enddate = Date.Now.Year.ToString + Date.Now.Month.ToString.PadLeft(2, "0") + enddate
                End If
            End If
            dtRes.Rows.Add(begindate, enddate)
            Return dtRes
        Catch ex As Exception
            Return New DataTable
        End Try

    End Function

    ''' <summary>
    '''如果工区长登入，显示本工区的人员，不支持分配
    '''如果班长登入，显示本班的人员
    '''如果本组登入，显示本组的人员
    '''如果工人登入，显示自己的工时，不支持分配
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSplitEmployeeData(ByVal dt As DataTable, ByVal m_UserInfo As CurrentUserInfo) As DataTable
        Dim strsql As String =
            <string>
                select e.employeeid,e.employeename,e.fullname,'0' totalworktime,'0' inworktime,null employeeworktimezsid  
                from employee e 
                left join teamemployee te on te.employeeid=e.employeeid
                left join team t on t.teamid=te.teamid
                left join shift s on s.shiftid=t.shiftid
                where 1=1
            </string>
        If m_UserInfo.Roles.Contains("组长") Then
            Dim teamid As String = dt.Rows(0)("teamid").ToString
            strsql += String.Format(" and t.teamid='{0}'", teamid)
        ElseIf m_UserInfo.Roles.Contains("班长") Then
            Dim shiftid As String = dt.Rows(0)("shiftid").ToString
            strsql += String.Format(" and s.shiftid='{0}'", shiftid)
        Else
            Return New DataTable
        End If

        Dim dtres As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        For Each dr As DataRow In dtres.Rows
            If dr("employeeid").ToString = dt.Rows(0)("employeeid").ToString Then
                dr("totalworktime") = dt.Rows(0)("totalworktime")
                dr("inworktime") = dt.Rows(0)("totalworktime")
                dr("employeeworktimezsid") = dt.Rows(0)("employeeworktimezsid")
            End If
        Next

        Return dtres
    End Function

    Public Function LoadSplitDetailRecord(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strsql As String =
            <string>
                     select e.fullname fromfullname,esp.splitworktime,e1.fullname tofullname
                     from employeeworktimezssplit esp
                     left join employeeworktimezs es on esp.employeeworktimezsid=es.employeeworktimezsid
                     left join employee e on e.employeeid=esp.fromemployeeid
                     left join employee e1 on e1.employeeid=esp.toemployeeid
                     where es.startdate='{0}' and es.enddate='{1}' and (esp.fromemployeeid='{2}' or esp.toemployeeid='{2}')
                     order by e.employeeid asc   
            </string>
        strsql = String.Format(strsql, para("beginDate"), para("endDate"), para("employeeID"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function


    ''' <summary>
    ''' 分出工时人员的工时 采用减法，分入人员的工时采用加法
    '''如果分入人员数据在employeeworktimezs不存在，将employeeworktimezs插入一条分入人员的记录
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SplitWorkTimeData(ByVal dt As DataTable, ByVal para As Dictionary(Of String, String)) As Boolean
        Try
            Dim sqlArrayList As New ArrayList
            Dim strsql As String = ""
            Dim employeeworktimezsid As String = dt.Select("employeeworktimezsid is not null")(0)("employeeworktimezsid")
            For Each dr As DataRow In dt.Rows
                If dr("EMPLOYEEID").ToString <> para("FromEmployeeId") Then
                    strsql = String.Format(" update employeeworktimezs set splitouttime=splitouttime+{0} where employeeid='{1}' and startdate='{2}' and enddate='{3}' ",
                                           dr("INWORKTIME").ToString, para("FromEmployeeId"), para("beginDate"), para("endDate"))
                    sqlArrayList.Add(strsql)
                    strsql = " insert into employeeworktimezssplit(fromemployeeid,toemployeeid,splitworktime,splitemployeeid,employeeworktimezsid) values('{0}','{1}','{2}','{3}','{4}')"
                    sqlArrayList.Add(String.Format(strsql, para("FromEmployeeId"), dr("EMPLOYEEID").ToString, dr("INWORKTIME").ToString, para("SplitEmployeeId"), employeeworktimezsid))

                    Dim strsqlExist As String = "select employeeid from employeeworktimezs where employeeid='{0}' and startdate='{1}' and enddate='{2}'  "
                    strsqlExist = String.Format(strsqlExist, dr("EMPLOYEEID").ToString, para("beginDate"), para("endDate"))
                    If (DatabaseHelper.Query_ReturnDatTable(strsqlExist).Rows.Count > 0) Then
                        strsql = String.Format(" update employeeworktimezs set splitintime=splitintime+{0} where employeeid='{1}'  and startdate='{2}' and enddate='{3}' ",
                                               dr("INWORKTIME").ToString, dr("EMPLOYEEID"), para("beginDate"), para("endDate"))
                        sqlArrayList.Add(strsql)
                    Else
                        strsql = String.Format(" insert into employeeworktimezs(employeeid, sumworktime, startdate, enddate,splitouttime, splitintime,employeeworktimezsid) values('{0}',0,'{1}','{2}',0,{3},'{4}')",
                                                dr("EMPLOYEEID").ToString, para("beginDate"), para("endDate"), dr("INWORKTIME"), System.Guid.NewGuid())
                        sqlArrayList.Add(strsql)
                    End If
                End If
            Next

            If sqlArrayList.Count > 0 Then
                DatabaseHelper.ExecuteSqlListTran(sqlArrayList)
                Return True
            End If

            Return False
        Catch ex As Exception
            Return False
        End Try
    End Function
End Class
